
Alter   PROC sp_create_update_atc_die_bank_lot(

	@LOT_NUMBER	VARCHAR(50),
	@PART_NUMBER	VARCHAR(50),
	@QTY_OUT	INT,
	@DATE_OUT	VARCHAR(50),
	@VENDOR_NAME	VARCHAR(50),
	@ATK_Qty	INT 	

)

AS
BEGIN

	DECLARE @PROCESS_STEP_ID 		BIGINT 
 	DECLARE @PREVIOUS_PROCESS_STEP_ID	BIGINT
 	DECLARE @LOCATION_ID 			INT
 	DECLARE @TEMP_LOT_ID 			BIGINT
	DECLARE @MAX_TIMESTAMP 			DATETIME
	DECLARE @LOT_CURRENT_STATUS 		VARCHAR(20)
	DECLARE @TEMP_QTY_OUT 			INT
	DECLARE @TEMP_QTY_IN  			INT
	DECLARE @TEMP_DATE_IN 			DATETIME
	DECLARE @TEMP_DATE_OUT 			DATETIME
	DECLARE @LOT_ID 			BIGINT
	DECLARE @SORT_LOTID			INT	



	DECLARE @WAFER_ID	INT 	
	DECLARE	@BIN_1	 	INT 
	DECLARE	@BIN_2	 	INT 
	DECLARE	@BIN_3	 	INT 
	DECLARE	@BIN_4		INT 
	DECLARE	@BIN_5		INT 
	DECLARE @BIN_6		INT 
	DECLARE	@BIN_7		INT 
	DECLARE	@BIN_8		INT 
	DECLARE	@BIN_9		INT 
	DECLARE	@BIN_10		INT 
	DECLARE	@BIN_11		INT 
	DECLARE	@BIN_12		INT 
	DECLARE	@BIN_13		INT 
	DECLARE	@BIN_14		INT 
	DECLARE	@BIN_15		INT 
	DECLARE	@BIN_1_BALANCE_QTY	  INT 
	DECLARE	@BIN_2_BALANCE_QTY	  INT 
	DECLARE	@BIN_3_BALANCE_QTY	  INT 
	DECLARE	@BIN_4_BALANCE_QTY	  INT 
	DECLARE	@BIN_5_BALANCE_QTY	  INT 
	DECLARE	@BIN_6_BALANCE_QTY	  INT 
	DECLARE	@BIN_7_BALANCE_QTY	  INT 
	DECLARE	@BIN_8_BALANCE_QTY	  INT 
	DECLARE	@BIN_9_BALANCE_QTY	  INT 
	DECLARE	@BIN_10_BALANCE_QTY	  INT 
	DECLARE	@BIN_11_BALANCE_QTY	  INT 
	DECLARE	@BIN_12_BALANCE_QTY	  INT 
	DECLARE	@BIN_13_BALANCE_QTY	  INT 
	DECLARE	@BIN_14_BALANCE_QTY	  INT 
	DECLARE	@BIN_15_BALANCE_QTY	  INT 
	DECLARE @IS_DOWN_GRADE_BIN	  INT 
	DECLARE @IS_DOWN_GRADE_BIN_PULLED INT
	DECLARE @ATC_QTY_OUT		  INT

	DECLARE @DIEBANKBINCURSOR CURSOR
	
	/* to get atk4 sort lotId */
	SELECT @SORT_LOTID = lot_id from LOT_TABLE WHERE LOT_NUMBER = @LOT_NUMBER AND LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_TYPE = 'SORT'  AND L_VENDOR_NAME = 'ATK4' )
	
	/* This code sending alert, when qty discrepancy is there in atk to atc */
	IF CAST( @QTY_OUT AS INT) <> CAST ( @ATK_QTY AS INT)
	BEGIN

		INSERT INTO SORT_DISCREPANCY_TABLE( PART_NUMBER, LOT_NUMBER, ATK_QTY, ATC_QTY )
		VALUES( @PART_NUMBER, @LOT_NUMBER, @ATK_QTY, @QTY_OUT) 	

	END

	
	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'SORT' AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
  	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'SORT' AND L_VENDOR_NAME = @VENDOR_NAME

	IF @LOCATION_ID = '' OR @LOCATION_ID IS NULL
	BEGIN
		-- DUMMY UPDATE TO AVOID SQLEXCEPTION
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		RETURN
	END
  
	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME LIKE 'SORT' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
		IF @PROCESS_STEP_ID IS NULL
		BEGIN
			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
			RETURN
		END
		
	END
	 	
	SELECT @TEMP_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	 	
	IF @TEMP_LOT_ID IS NOT NULL
 	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close', LOT_ENTERED_BY = 1  WHERE LOT_ID = @TEMP_LOT_ID
		SELECT @MAX_TIMESTAMP = MAX(SORT_LOT_HISTORY.TIMESTAMP) FROM SORT_LOT_HISTORY WHERE LOT_ID = @TEMP_LOT_ID 
			
		IF @MAX_TIMESTAMP IS NOT NULL
		BEGIN
			SELECT @LOT_CURRENT_STATUS = SL_CURRENT_STATUS, @TEMP_QTY_OUT = SL_DIE_QTY_OUT  FROM SORT_LOT_HISTORY WHERE LOT_ID = @TEMP_LOT_ID AND SORT_LOT_HISTORY.TIMESTAMP = @MAX_TIMESTAMP 
			IF @LOT_CURRENT_STATUS = 'Close'
			BEGIN
				IF @TEMP_QTY_OUT = @QTY_OUT
				BEGIN
					UPDATE SORT_LOT_HISTORY SET SL_DATE_OUT = @DATE_OUT, SL_DIE_QTY_OUT = @QTY_OUT WHERE LOT_ID = @TEMP_LOT_ID AND SORT_LOT_HISTORY.TIMESTAMP = CURRENT_TIMESTAMP AND SL_CURRENT_STATUS = 'Close'
				END
				ELSE
				BEGIN
					UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT NUMBER' WHERE LOT_NUMBER = 'LOT NUMBER'
				END	
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT NUMBER' WHERE LOT_NUMBER = 'LOT NUMBER'
			END
		END
		ELSE 
		BEGIN
			INSERT INTO SORT_LOT_HISTORY ( LOT_ID, SL_DIE_QTY_OUT, SL_DATE_OUT, TIMESTAMP, SL_CURRENT_STATUS ) VALUES ( @TEMP_LOT_ID, @QTY_OUT,  @DATE_OUT, CURRENT_TIMESTAMP,'Close')
		END
	END
		
	SET @MAX_TIMESTAMP = NULL
	PRINT @TEMP_LOT_ID
	SET @LOT_ID = @TEMP_LOT_ID

	IF @PROCESS_STEP_ID IS NOT NULL AND @LOT_ID IS NULL 
 	BEGIN
 		INSERT INTO LOT_TABLE ( PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, LOT_CURRENT_STATUS,LOT_ENTERED_BY, TIMESTAMP) VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, 'Close' ,1, CURRENT_TIMESTAMP)
 		SET @LOT_ID = @@IDENTITY
		INSERT INTO SORT_LOT_HISTORY ( LOT_ID, SL_DIE_QTY_OUT, SL_DATE_OUT, TIMESTAMP, SL_CURRENT_STATUS ) VALUES ( @LOT_ID, @QTY_OUT,  @DATE_OUT, CURRENT_TIMESTAMP,'Close')
		INSERT INTO NEW_LOT_ID_TABLE ( LOT_ID, LOT_NUMBER, PART_NUMBER , PROCESS_TYPE, TIMESTAMP, VENDOR_NAME, QTY_IN ) VALUES ( @LOT_ID, @LOT_NUMBER, @PART_NUMBER ,'Die Bank', CURRENT_TIMESTAMP, @VENDOR_NAME, @QTY_OUT )
		--Wafers in the Informative_Binning are moved to Informative_Binning_History when the lot is transferred from ATK to ATC.
		--The wafers in the Informative_Binning will point to the ATC lot and the wafers in the Informative_Binning_History
		--will point to the ATK
		INSERT INTO INFORMATIVE_BINNING_HISTORY SELECT * FROM INFORMATIVE_BINNING WHERE LOT_ID = @SORT_LOTID
		UPDATE INFORMATIVE_BINNING SET LOT_ID = @LOT_ID WHERE LOT_ID = @SORT_LOTID
 	END

			
	DECLARE @DIEBANKCOUNT INT
	SET @DIEBANKCOUNT = 0 
	/*Moving the Lots from ATK4 to ATC1*/
	IF @QTY_OUT > 0 and @LOT_ID > 0
	BEGIN

		
		SELECT @DIEBANKCOUNT = COUNT(*) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID
		
		IF ( @DIEBANKCOUNT = 0 )
		BEGIN
			SET @DIEBANKBINCURSOR = CURSOR FOR SELECT WAFER_ID, BIN_1, BIN_2, BIN_3, BIN_4, BIN_5, BIN_6, BIN_7, BIN_8, BIN_9, BIN_10, BIN_11, BIN_12, BIN_13, BIN_14, BIN_15, BIN_1_BALANCE_QTY, BIN_2_BALANCE_QTY, BIN_3_BALANCE_QTY, BIN_4_BALANCE_QTY, BIN_5_BALANCE_QTY, BIN_6_BALANCE_QTY, BIN_7_BALANCE_QTY, BIN_8_BALANCE_QTY, BIN_9_BALANCE_QTY, BIN_10_BALANCE_QTY, BIN_11_BALANCE_QTY, BIN_12_BALANCE_QTY, BIN_13_BALANCE_QTY, BIN_14_BALANCE_QTY, BIN_15_BALANCE_QTY, IS_DOWN_GRADE_BIN, IS_DOWN_GRADE_BIN_PULLED FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @SORT_LOTID
			OPEN @DIEBANKBINCURSOR 
			FETCH NEXT FROM @DIEBANKBINCURSOR INTO @WAFER_ID, @BIN_1, @BIN_2, @BIN_3, @BIN_4, @BIN_5, @BIN_6, @BIN_7, @BIN_8, @BIN_9, @BIN_10, @BIN_11, @BIN_12, @BIN_13, @BIN_14, @BIN_15, @BIN_1_BALANCE_QTY, @BIN_2_BALANCE_QTY, @BIN_3_BALANCE_QTY, @BIN_4_BALANCE_QTY, @BIN_5_BALANCE_QTY, @BIN_6_BALANCE_QTY, @BIN_7_BALANCE_QTY, @BIN_8_BALANCE_QTY, @BIN_9_BALANCE_QTY, @BIN_10_BALANCE_QTY, @BIN_11_BALANCE_QTY, @BIN_12_BALANCE_QTY, @BIN_13_BALANCE_QTY, @BIN_14_BALANCE_QTY, @BIN_15_BALANCE_QTY, @IS_DOWN_GRADE_BIN, @IS_DOWN_GRADE_BIN_PULLED 
			WHILE @@FETCH_STATUS = 0
			BEGIN 
				INSERT INTO SORT_BIN_RELEASE_TABLE ( LOT_ID, WAFER_ID, BIN_1, BIN_2, BIN_3, BIN_4, BIN_5, BIN_6, BIN_7, BIN_8, BIN_9, BIN_10, BIN_11, BIN_12, BIN_13, BIN_14, BIN_15, BIN_1_BALANCE_QTY, BIN_2_BALANCE_QTY, BIN_3_BALANCE_QTY, BIN_4_BALANCE_QTY, BIN_5_BALANCE_QTY, BIN_6_BALANCE_QTY, BIN_7_BALANCE_QTY, BIN_8_BALANCE_QTY, BIN_9_BALANCE_QTY, BIN_10_BALANCE_QTY, BIN_11_BALANCE_QTY, BIN_12_BALANCE_QTY, BIN_13_BALANCE_QTY, BIN_14_BALANCE_QTY, BIN_15_BALANCE_QTY, IS_DOWN_GRADE_BIN, IS_DOWN_GRADE_BIN_PULLED)
				VALUES ( @LOT_ID, @WAFER_ID, @BIN_1, @BIN_2, @BIN_3, @BIN_4, @BIN_5, @BIN_6, @BIN_7, @BIN_8, @BIN_9, @BIN_10, @BIN_11, @BIN_12, @BIN_13, @BIN_14, @BIN_15, @BIN_1_BALANCE_QTY, @BIN_2_BALANCE_QTY, @BIN_3_BALANCE_QTY, @BIN_4_BALANCE_QTY, @BIN_5_BALANCE_QTY, @BIN_6_BALANCE_QTY, @BIN_7_BALANCE_QTY, @BIN_8_BALANCE_QTY, @BIN_9_BALANCE_QTY, @BIN_10_BALANCE_QTY, @BIN_11_BALANCE_QTY, @BIN_12_BALANCE_QTY, @BIN_13_BALANCE_QTY, @BIN_14_BALANCE_QTY, @BIN_15_BALANCE_QTY, @IS_DOWN_GRADE_BIN, @IS_DOWN_GRADE_BIN_PULLED ) 
			
			SET @WAFER_ID	= 0
			SET @BIN_1	= 0
			SET @BIN_2	= 0
			SET @BIN_3	= 0
			SET @BIN_4	= 0
			SET @BIN_6	= 0
			SET @BIN_7	= 0
			SET @BIN_8	= 0 
			SET @BIN_9	= 0
			SET @BIN_10	= 0
			SET @BIN_11	= 0
			SET @BIN_12	= 0
			SET @BIN_13	= 0
			SET @BIN_14	= 0
			SET @BIN_15	= 0
			SET @BIN_1_BALANCE_QTY	= 0
			SET @BIN_2_BALANCE_QTY	= 0
			SET @BIN_3_BALANCE_QTY	= 0
			SET @BIN_4_BALANCE_QTY	= 0
			SET @BIN_5_BALANCE_QTY	= 0
			SET @BIN_6_BALANCE_QTY	= 0
			SET @BIN_7_BALANCE_QTY	= 0
			SET @BIN_8_BALANCE_QTY	= 0
			SET @BIN_9_BALANCE_QTY	= 0
			SET @BIN_10_BALANCE_QTY	= 0
			SET @BIN_11_BALANCE_QTY	= 0
			SET @BIN_12_BALANCE_QTY	= 0
			SET @BIN_13_BALANCE_QTY	= 0
			SET @BIN_14_BALANCE_QTY	= 0
			SET @BIN_15_BALANCE_QTY	= 0
	
			FETCH NEXT FROM @DIEBANKBINCURSOR INTO @WAFER_ID, @BIN_1, @BIN_2, @BIN_3, @BIN_4, @BIN_5, @BIN_6, @BIN_7, @BIN_8, @BIN_9, @BIN_10, @BIN_11, @BIN_12, @BIN_13, @BIN_14, @BIN_15, @BIN_1_BALANCE_QTY, @BIN_2_BALANCE_QTY, @BIN_3_BALANCE_QTY, @BIN_4_BALANCE_QTY, @BIN_5_BALANCE_QTY, @BIN_6_BALANCE_QTY, @BIN_7_BALANCE_QTY, @BIN_8_BALANCE_QTY, @BIN_9_BALANCE_QTY, @BIN_10_BALANCE_QTY, @BIN_11_BALANCE_QTY, @BIN_12_BALANCE_QTY, @BIN_13_BALANCE_QTY, @BIN_14_BALANCE_QTY, @BIN_15_BALANCE_QTY, @IS_DOWN_GRADE_BIN, @IS_DOWN_GRADE_BIN_PULLED 
			END
			CLOSE @DIEBANKBINCURSOR
			DEALLOCATE @DIEBANKBINCURSOR 
		END
		UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = 0, BIN_2_BALANCE_QTY = 0,BIN_3_BALANCE_QTY = 0,BIN_4_BALANCE_QTY = 0,BIN_5_BALANCE_QTY = 0,BIN_6_BALANCE_QTY = 0,BIN_7_BALANCE_QTY = 0,BIN_8_BALANCE_QTY = 0,BIN_9_BALANCE_QTY = 0,BIN_10_BALANCE_QTY = 0,BIN_11_BALANCE_QTY = 0,BIN_12_BALANCE_QTY = 0,BIN_13_BALANCE_QTY = 0,BIN_14_BALANCE_QTY = 0,BIN_15_BALANCE_QTY = 0 WHERE LOT_ID = @SORT_LOTID
	END
	ELSE
	BEGIN
	
		RETURN	
			
	END
END
